﻿using DataAccess;
using Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Business
{
    public class ProductsBusiness
    {
        public object GetCount()
        {
            object res = null;
            string query = "select count(1) from products";
            res = DA.GetOneData(query, CommandType.Text, null, null);
            return res;
        }

        public DataTable GetAllData()
        {
            DataTable dt = null;
            string query = "select * from products";
            dt = DA.GetDataTable(query, CommandType.Text, null, null);
            return dt;
        }

        public DataTable GetPageData(int pageNum,int pageCount)
        {
            DataTable dt = null;
            string query = "select * from products order by productid offset @pageNum rows fetch next @pageCount rows only";
            string[] paras = { "@pageNum", "@pageCount" };
            object[] values = { pageNum, pageCount };
            dt = DA.GetDataTable(query, CommandType.Text, paras, values);
            return dt;
        }

        public DataTable GetDataDetails(string productID)
        {
            DataTable dt = null;
            string query = "select * from products where productid = @productid";
            string[] paras = { "@productid" };
            object[] values = { productID };
            dt = DA.GetDataTable(query, CommandType.Text, paras, values);
            return dt;
        }

        public DataTable GetSpecialData()
        {
            DataTable dt = null;
            string query = "SELECT TOP 5 * FROM products ORDER BY NEWID()";
            dt = DA.GetDataTable(query, CommandType.Text, null, null);
            return dt;
        }

        public int AddProduct(ProductsEntity pe)
        {
            int res = 0;
            string query = "insert into products values (@productname,@price,@description,@stockquantity,@salesquantity,@image)";
            string[] paras = { "@productname", "@price", "@description", "@stockquantity", "@salesquantity", "@image" };
            object[] values = { pe.Productname, pe.Price, pe.Description, pe.Stockquantity, pe.Salesquantity, pe.Image };
            res = DA.ExcuteSql(query, CommandType.Text, paras, values);
            return res;
        }

        public int DeleteProduct(ProductsEntity pe)
        {
            int res = 0;
            string query = "delete from products where productid = @productid";
            string[] paras = { "@productid" };
            object[] values = { pe.Productid };
            res = DA.ExcuteSql(query, CommandType.Text, paras, values);
            return res;
        }

        public int DeleteMoreProducts(string sql)
        {
            int res = 0;
            string query = "delete from products where 1>1 " + sql;
            res = DA.ExcuteSql(query, CommandType.Text, null, null);
            return res;
        }

        public int EditProduct(ProductsEntity pe)
        {
            int res = 0;
            string query = "update products set productname=@productname,description=@description,price=@price,stockquantity=@stockquantity,salesquantity=@salesquantity,image=@image where productid=@productid";
            string[] paras = { "@productname", "description", "@price", "@stockquantity", "@salesquantity", "@image", "@productid" };
            object[] values = { pe.Productname, pe.Description, pe.Price, pe.Stockquantity, pe.Salesquantity, pe.Image, pe.Productid };
            res = DA.ExcuteSql(query, CommandType.Text, paras, values);
            return res;
        }

        public int ResetProduct(ProductsEntity pe)
        {
            int res = 0;
            string query = "update products set stockquantity-=@salesquantity,salesquantity+=@salesquantity where productid=@productid";
            string[] paras = { "@salesquantity", "@productid" };
            object[] values = { pe.Salesquantity, pe.Productid };
            res = DA.ExcuteSql(query, CommandType.Text, paras, values);
            return res;
        }

        public int ResetProductByProductName(ProductsEntity pe)
        {
            int res = 0;
            string query = "update products set stockquantity-=@salesquantity,salesquantity+=@salesquantity where productname=@productname";
            string[] paras = { "@salesquantity", "@productname" };
            object[] values = { pe.Salesquantity, pe.Productname };
            res = DA.ExcuteSql(query, CommandType.Text, paras, values);
            return res;
        }

        public DataTable SearchAllData(ProductsEntity pe)
        {
            DataTable dt = null;
            string query = "select * from products where productname like @productname";
            string[] paras = { "@productname" };
            object[] values = { "%" + pe.Productname + "%" };
            dt = DA.GetDataTable(query, CommandType.Text, paras, values);
            return dt;
        }
    }
}
